package cn.swenty.backstage.framework.database.mybatis.sql;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.session.Configuration;

import cn.swenty.backstage.framework.cache.core.eh.CacheClassify;
import cn.swenty.backstage.framework.cache.core.eh.EhcacheFactory;
import cn.swenty.backstage.framework.common.util.StringUtil;
import cn.swenty.backstage.framework.database.annotation.TableSeg;
import cn.swenty.backstage.framework.database.entity.Entity;
import cn.swenty.backstage.framework.database.mybatis.enums.DATABASE;
import cn.swenty.backstage.framework.database.mybatis.enums.SELECT;
import cn.swenty.backstage.framework.database.plugin.PageView;
import cn.swenty.backstage.framework.util.RegexUtil;

public class SqlHelp {


	public static String getSelectTotalSql(String targetSql, DATABASE dialect) {

		String sql = targetSql.toLowerCase();
		StringBuilder sqlBuilder = new StringBuilder(sql);
		int orderByPos = 0;
		switch (dialect) {
		case SQLSERVER2008:
			if ((orderByPos = sqlBuilder.lastIndexOf("order by")) != -1) {
				sqlBuilder.delete(orderByPos, sqlBuilder.length());
			}

			if (sqlBuilder.indexOf("union") != -1) {
				sqlBuilder.insert(0, "select count(1) as _count from ( ").append(" ) as _alias");
				return sqlBuilder.toString();
			}

			int fromPos = sqlBuilder.indexOf("from");
			if (fromPos != -1) {
				sqlBuilder.delete(0, fromPos);
				sqlBuilder.insert(0, "select count(1) as _count ");
			}
			break;
		case ORACLE:
			if ((orderByPos = sqlBuilder.lastIndexOf("order by")) != -1) {
				sqlBuilder.delete(orderByPos, sqlBuilder.length());
			}
			sqlBuilder.insert(0, "select count(1) as _count from ( ").append(" ) as _alias");
			break;
		case MYSQL:
			if ((orderByPos = sqlBuilder.lastIndexOf("order by")) != -1) {
				sqlBuilder.delete(orderByPos, sqlBuilder.length());
			}
			sqlBuilder.insert(0, "select count(1) as _count from ( ").append(" ) as _alias");
			break;
		default:
			throw new RuntimeException("can not support database '" + dialect + "'");
		}

		return sqlBuilder.toString();

	}

	public static String getSelectPagingSql(String targetSql, DATABASE dialect,String orderby, int offset, int limit) {
		StringBuilder sqlBuilder = new StringBuilder(targetSql.toLowerCase()+" "+(StringUtil.isNotNull(orderby)?orderby.toLowerCase():""));
		switch (dialect) {
		case SQLSERVER2008:
			if (sqlBuilder.indexOf("order by") != -1) {
				int selectPos = sqlBuilder.indexOf("select");
				sqlBuilder.insert(selectPos + "select".length(), " TOP(" + offset + limit + ")");
			}

			sqlBuilder.insert(0,
					"SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __mybatis_row_nr__ FROM ( ");
			sqlBuilder.append(" ) inner_query ");

			sqlBuilder.insert(0, "WITH query AS (").append(") SELECT ").append("*").append(" FROM query ");
			sqlBuilder.append(
					"WHERE __mybatis_row_nr__ >= " + (offset + 1) + " AND __mybatis_row_nr__ <= " + (offset + limit));
			break;
		case ORACLE:
			sqlBuilder.insert(0, "select * from ( select table_alias.*, rownum mybatis_rowNo from (");
			sqlBuilder.append(") ");
			sqlBuilder.append("table_alias where rownum <= " + (offset + limit)).append(")");
			sqlBuilder.append("where mybatis_rowNo >= " + (offset + 1));
			break;
		case MYSQL:
			sqlBuilder.append(" limit " + offset + "," + limit);
			break;
		default:
			throw new RuntimeException("can not support database '" + dialect + "'");
		}
		return sqlBuilder.toString();

	}
	
	public static String updateFullSql(Entity entity){
		String fields = getFields(entity);
		String fs[] = fields.split(",");
		for(int i=0;i<fs.length;i++){
			if(entity.get(fs[i]) == null){
				entity.put(fs[i], null);
			}
		}
		return updateSql(entity);
	}
	
	
	public static String updateSql(Entity entity){
		StringBuilder sb = new StringBuilder("");
		String table = getTable(entity);
		sb.append("update "+table+"  ");
		Iterator<String> it = entity.keySet().iterator();
		int i = 0;
		while (it.hasNext()) {
			String key = it.next();
			if(key.equals(getTablePK(entity))){
				continue;
			}
			if (!key.startsWith("$") && i == 0) {
				sb.append(" set " + key + " =  #{"+key+"}");
			}else if(!key.startsWith("$") && i > 0){
				if(hasField(entity, key)){
					sb.append(" , " + key + " =  #{"+key+"}");
				}
			}
			i++;
		} 
		String pk = getTablePK(entity);
		sb.append(" where "+pk+" = #{"+pk+"}");
		return sb.toString();
	}
	
	public static String deleteSql(Entity entity){
		StringBuilder sb = new StringBuilder("");
		String table = getTable(entity);
		sb.append("delete from "+table);
		sb.append(" where 1=1 ");
		Iterator<String> it = entity.keySet().iterator();
		while (it.hasNext()) {
			String key = it.next();
			if (!key.startsWith("$")) {
				sb.append(" and " + key + " = #{"+key+"}");
			}
		}
		return sb.toString();
	}
	
	public static String selectPageSql(DATABASE db,Entity entity){
		StringBuilder sb = new StringBuilder("");
		String table = getTable(entity);
		sb.insert(0, "select ").append(getFields(entity)).append(" from ").append(table).append(" where 1=1 ");
		Iterator<String> it = entity.keySet().iterator();
		while (it.hasNext()) {
			String key = it.next();
			if (!key.startsWith("$")) {
					sb.append(" and " + key + " = #{"+key+"}");
			}else{
				if(key.startsWith(SELECT.LIKE.getCode())){
					entity.put(key, ("%"+entity.get(key)+"%").replace("%%", "%"));
					sb.append(" and " +SELECT.LIKE.replace(key) + " like #{"+key+"}");
				}else if(key.startsWith(SELECT.LEFT.getCode())){
					entity.put(key, (entity.get(key)+"%").replace("%%", "%"));
					sb.append(" and " + SELECT.LEFT.replace(key) + " like #{"+key+"}");
				}else if(key.startsWith(SELECT.RIGHT.getCode())){
					entity.put(key, ("%" + entity.get(key)).replace("%%", "%"));
					sb.append(" and " + SELECT.RIGHT.replace(key) + " like #{"+key+"}");
				}else if(key.startsWith(SELECT.EQ.getCode())){
					sb.append(" and " + SELECT.EQ.replace(key) + " = #{"+key+"}");
				}else if(key.startsWith(SELECT.GT.getCode())){
					sb.append(" and " + SELECT.GT.replace(key) + " >= #{"+key+"}");
				}else if(key.startsWith(SELECT.LT.getCode())){
					sb.append(" and " + SELECT.LT.replace(key) + " <= #{"+key+"}");
				}else if(key.startsWith(SELECT.IN.getCode())){
					sb.append(" and " + SELECT.IN.replace(key) + " in ( #{"+key+"})");
				}else if(key.startsWith(SELECT.NIN.getCode())){
					sb.append(" and " + SELECT.NIN.replace(key) + " not in ( #{"+key+"})");
				}else if(key.startsWith("$sql")){
					sb.append(" and "+entity.getStr("$sql")+" ");
				}
			}
		}
		PageView pv = (PageView)(entity.get(Entity.PAGE_VIEW));
		return getSelectPagingSql(sb.toString(), db,pv.getOrderby(), pv.getFirstResult(), pv.getPageSize());
	}

	public static String selectSql(Entity entity) {
		StringBuilder sb = new StringBuilder("");
		String table = getTable(entity);
		sb.insert(0, "select ").append(getFields(entity)).append(" from ").append(table).append(" where 1=1 ");
		Iterator<String> it = entity.keySet().iterator();
		while (it.hasNext()) {
			String key = it.next();
			if (!key.startsWith("$")) {
				sb.append(" and " + key + " = #{"+key+"}");
			}else{
				if(key.startsWith(SELECT.LIKE.getCode())){
					entity.put(key, ("%"+entity.get(key)+"%").replace("%%", "%"));
					sb.append(" and " +SELECT.LIKE.replace(key) + " like #{"+key+"}");
				}else if(key.startsWith(SELECT.LEFT.getCode())){
					entity.put(key, (entity.get(key)+"%").replace("%%", "%"));
					sb.append(" and " + SELECT.LEFT.replace(key) + " like #{"+key+"}");
				}else if(key.startsWith(SELECT.RIGHT.getCode())){
					entity.put(key, ("%" + entity.get(key)).replace("%%", "%"));
					sb.append(" and " + SELECT.RIGHT.replace(key) + " like #{"+key+"}");
				}else if(key.startsWith(SELECT.EQ.getCode())){
					sb.append(" and " + SELECT.EQ.replace(key) + " = #{"+key+"}");
				}else if(key.startsWith(SELECT.GT.getCode())){
					sb.append(" and " + SELECT.GT.replace(key) + " >= #{"+key+"}");
				}else if(key.startsWith(SELECT.LT.getCode())){
					sb.append(" and " + SELECT.LT.replace(key) + " <= #{"+key+"}");
				}else if(key.startsWith(SELECT.IN.getCode())){
					sb.append(" and " + SELECT.IN.replace(key) + " in ( #{"+key+"})");
				}else if(key.startsWith(SELECT.NIN.getCode())){
					sb.append(" and " + SELECT.NIN.replace(key) + " not in ( #{"+key+"})");
				}else if(key.startsWith("$sql")){
					sb.append(" and "+entity.getStr("$sql")+" ");
				}
			}
		}
		if(entity.get("$orderby") != null){
			sb.append(" "+entity.getStr("$orderby")+" ");
		}
		return sb.toString();
	}
	
	public static String selectCountSql(DATABASE db,Entity entity) {
		StringBuilder sb = new StringBuilder("");
		String table = getTable(entity);
		sb.insert(0, "select ").append(getFields(entity)).append(" from ").append(table).append(" where 1=1 ");
		Iterator<String> it = entity.keySet().iterator();
		while (it.hasNext()) {
			String key = it.next();
			if (!key.startsWith("$")) {
				sb.append(" and " + key + " = #{"+key+"}");
			}else{
				if(key.startsWith(SELECT.LIKE.getCode())){
					entity.put(key, ("%"+entity.get(key)+"%").replace("%%", "%"));
					sb.append(" and " +SELECT.LIKE.replace(key) + " like #{"+key+"}");
				}else if(key.startsWith(SELECT.LEFT.getCode())){
					entity.put(key, (entity.get(key)+"%").replace("%%", "%"));
					sb.append(" and " + SELECT.LEFT.replace(key) + " like #{"+key+"}");
				}else if(key.startsWith(SELECT.RIGHT.getCode())){
					entity.put(key, ("%" + entity.get(key)).replace("%%", "%"));
					sb.append(" and " + SELECT.RIGHT.replace(key) + " like #{"+key+"}");
				}else if(key.startsWith(SELECT.EQ.getCode())){
					sb.append(" and " + SELECT.EQ.replace(key) + " = #{"+key+"}");
				}else if(key.startsWith(SELECT.GT.getCode())){
					sb.append(" and " + SELECT.GT.replace(key) + " >= #{"+key+"}");
				}else if(key.startsWith(SELECT.LT.getCode())){
					sb.append(" and " + SELECT.LT.replace(key) + " <= #{"+key+"}");
				}else if(key.startsWith(SELECT.IN.getCode())){
					sb.append(" and " + SELECT.IN.replace(key) + " in ( #{"+key+"})");
				}else if(key.startsWith(SELECT.NIN.getCode())){
					sb.append(" and " + SELECT.NIN.replace(key) + " not in ( #{"+key+"})");
				}else if(key.startsWith("$sql")){
					sb.append(" and "+entity.getStr("$sql")+" ");
				}
			}
		}
		return SqlHelp.getSelectTotalSql(sb.toString(), db);
	}

	public static String insertSql(Entity entity) {
		StringBuilder sb = new StringBuilder("");
		String table = getTable(entity);
		sb.insert(0, "insert into  ").append(table).append(" ( ");
		Iterator<String> it = entity.keySet().iterator();
		String ifield = "";
		String vfield = "";
		int i = 0;
		while (it.hasNext()) {
			String key = it.next();
			if (!key.startsWith("$") && hasField(entity, key)) {
				if (i > 0) {
					ifield += "," + key;
					vfield += ", #{"+key+"}";
				} else {
					ifield = key;
					vfield = " #{"+key+"}";
				}
				i++;
			}
			
		}
		sb.append(ifield);
		sb.append(" ) ");
		sb.append(" values(");
		sb.append(vfield);
		sb.append(")");
		return sb.toString();
	}
	
	public static String buildSql(String sql,Entity entity){
		Iterator<String> it = entity.keySet().iterator();
		while(it.hasNext()){
			String key = it.next();
			Object value = entity.get(key);
			if(key !=null && (key.startsWith(SELECT.IN.getCode()) || key.startsWith(SELECT.NIN.getCode())) && value != null){
				if(value.getClass().isArray() && ((Object[])value).length>0){
					String strp = "";
					Object[] os = (Object[])value;
					for(int i=0;i<os.length;i++){
						if(i==0){
							strp = "?";
						}else{
							strp+=",?";
						}
					}
					sql = sql.replace("#{"+key+"}", strp);
				}else{
					sql = sql.replace("#{"+key+"}", "?");
				}
			}else if(key !=null ){
				sql = sql.replace("#{"+key+"}", "?");
			}
			
			
		}
		return sql.replaceAll("#\\{[\\w|\\$]+}", " ? ");
	}
	public static List<ParameterMapping> bulidSqlParm(String sql,Configuration c, Entity entity) {
		List<String> list = RegexUtil.matchList(sql, "#\\{[\\w|\\$]+\\}");
		List<ParameterMapping> paras = new ArrayList<>();
		for(int i=0;i<list.size();i++){
			String key = list.get(i).replaceAll("^#\\{", "").replaceAll("\\}$", "");
			Object value = entity.get(key);
			if((key.startsWith(SELECT.IN.getCode()) || key.startsWith(SELECT.NIN.getCode())) && value!=null && value.getClass().isArray()){
				Object[] os = (Object[])value;
				for(int j=0;j<os.length;j++){
					ParameterMapping mapping = new ParameterMapping.Builder(c, "$"+j+"_"+key, os[j]==null?String.class:os[j].getClass()).build();
					entity.put("$"+j+"_"+key, os[j]);
					paras.add(mapping);
				}
			}else{
				ParameterMapping mapping = new ParameterMapping.Builder(c, key, entity.get(key)==null?String.class:entity.get(key).getClass()).build();
				paras.add(mapping);
			}
			
		}
		return paras;
	}
	
	public static String sqlParm( List<ParameterMapping>  pms) {
		StringBuilder pars = new StringBuilder("{}");
		for(int i=0;i<pms.size();i++){
			ParameterMapping pm = pms.get(i);
			pars.insert(1, pm.getProperty());
			if(i != 0){
				pars.insert(pars.length()-1, ",");
			}
		}
		
		return pars.toString();
	}

	public static String getTablePK(Entity entity){

		Class<?> clazz = entity.getClass();
		boolean flag = clazz.isAnnotationPresent(TableSeg.class);
		if (flag) {
			TableSeg table = (TableSeg) clazz.getAnnotation(TableSeg.class);
			return table.id();
		}
		return null;
	
	}
	
	public static String getTable(Entity entity) {
		Class<?> clazz = entity.getClass();
		boolean flag = clazz.isAnnotationPresent(TableSeg.class);
		if (flag) {
			TableSeg table = (TableSeg) clazz.getAnnotation(TableSeg.class);
			return StringUtil.isNull(table.database()) ? table.tableName()
					: (table.database() + "." + table.tableName());
		}
		return null;
	}
	
	public static boolean hasField(Entity entity,String field){
		String strfields = getFields(entity);
		String fields[] = strfields.split(",");
		for(String f:fields){
			if(StringUtil.isNotNull(f) && f.toLowerCase().equals(field)){
				return true;
			}
		}
		return false;
	}

	public static String getFields(Entity entity) {
		String table = getTable(entity);
		if (table == null) {
			return null;
		}
		Map<String, Object> tables = (Map<String, Object>) EhcacheFactory.get(CacheClassify.TABLE);
		return (String) ((Map<String, Object>)tables.get(table)).get("fields");

	}

}
